﻿using FreesenseSubAIS.Core;
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using FreesenseSubAIS.InspProcess;
using System.Diagnostics;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Reflection;
using FreesenseSubAIS.ImageProcess;
using System.Data;
using NPOI.XSSF.UserModel;
using NLog;

namespace FreesenseSubAIS
{
    public class ExcelHandle
    {
        List<string> conststr;
        ILogger _log;
        public ExcelHandle()
        {
            _log = LogManager.GetLogger("main");
            conststr = new List<string>();
            conststr.Add("BatchID");
            conststr.Add("PanelID");
            conststr.Add("OperatorID");
            conststr.Add("LineNO");
            conststr.Add("StartTime");
            conststr.Add("Duration");
            conststr.Add("Result");
            conststr.Add("ErrCode");
        }
        #region 保存检测记录
        public bool SaveRecord(InspRecord results, string cID)
        {
            if (!string.IsNullOrEmpty(Device.Parameter.RemoteLogPath))
            {
                if (Device.Automode && Device.Parameter.MseEnable)
                    SaveRemoteFormatLog(results);
            }
            return SaveLocalDataForExcel(results, cID);
        }
        private void SaveRemoteFormatLog(InspRecord results)
        {
            if (Device.Parameter.MesClient == null || Device.Parameter.MesClient.MesList == null || Device.Parameter.MesClient.MesList.Count < 1)
            {
                return;
            }
            try
            {
                var keyarray = ("ProjectName,ID,Result,BatchCode,OperatorID,EquipID,LineID,InpectingTime,InspectStartTime,InspectEndTime,ErrCode").Split(',');
                var valuearray = string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10}",
                    Device.defaultProduct.ProductName,
                    results.PannelID,
                    results.InspResult == 1 ? "OK" : "NG",
                    results.BatchID,
                    results.OperatorID,
                    results.EquipID,
                    results.LineNO,
                    results.Duaration,
                    results.StartTime,
                    results.EndTime,
                    results.ErrCode).Split(',');
                StringBuilder sb1 = new StringBuilder();
                StringBuilder sb2 = new StringBuilder();
                for (int i = 0; i < Device.Parameter.MesClient.MesList.Count; i++)
                {
                    if (Device.Parameter.MesClient.MesList[i].Item3)
                    {
                        sb1.Append(Device.Parameter.MesClient.MesList[i].Item2 + ((i == Device.Parameter.MesClient.MesList.Count - 1) ? "" : ","));
                        sb2.Append(valuearray[keyarray.ToList().FindIndex(x => x.Equals(Device.Parameter.MesClient.MesList[i].Item2))] + ((i == Device.Parameter.MesClient.MesList.Count - 1) ? "" : ","));
                    }
                }
                string filenamemode = "";
                //if (Device.Parameter.MesClient.FileNameMode == 0)
                //    filenamemode = string.Format("{0}_{1}{2}", (Device.Parameter.OKCount + Device.Parameter.NGCount), results.StartTime, Device.Parameter.MesClient.FileMode);
                //else
                filenamemode = string.Format("{0}_{1}{2}", Device.Parameter.EquipID, results.StartTime, Device.Parameter.MesClient.FileMode);
                SystemCommon.SaveLogFile(sb1.ToString() + "\r\n" + sb2.ToString(), Path.Combine(Device.Parameter.RemoteLogPath, DateTime.Now.ToString("yyyyMMdd"), filenamemode));
            }
            catch (Exception ex)
            {
            }
        }
        private void SaveRemoteLog(InspRecord results)
        {
            StringBuilder sb1 = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            sb1.AppendFormat("PANELID={{{0}}};", results.PannelID);
            sb1.AppendFormat("OPERATORID={{{0}}};", results.OperatorID);
            sb1.AppendFormat("STARTTIME={{{0}}};", results.StartTime);
            sb1.AppendFormat("BATCHID={{{0}}};", results.BatchID);
            sb1.AppendFormat("LINE={{{0}}};", results.LineNO);
            sb2.Append(sb1.ToString());
            int i = 1;
            foreach (ResultDisplayRowResult item in results.ListResults)
            {
                sb1.AppendFormat("ROW{0}={{TESTTYPE=[Check];ITEMNAME=[{1}];ITEMMSG=[{2}];ITEMRESULT=[{3}];}};"
                    , (i).ToString("d4")
                    , item.ItemName
                    , item.Msg
                    , item.Result ? "OK" : "NG"
                    );
                i++;
            }
            string endStr = string.Format("MODULE={{AIS}};RESULT={{{0}}};DURATION={{{1} sec.}};", results.InspResult == 1 ? "PASS" : "Fail", results.Duaration);
            sb1.Append(endStr);
            sb2.Append(endStr);
            SystemCommon.SaveLogFile(sb1.ToString(), Path.Combine(Device.Parameter.RemoteLogPath, string.Format("AIS_{0}.log", results.PannelID)));
            SystemCommon.SaveLogFile(sb2.ToString(), Path.Combine(Device.Parameter.RemoteLogPath, string.Format("AIS_{0}.txt", results.PannelID)));
        }
        public bool SaveLocalDataForExcel(InspRecord results, string cID)
        {
            try
            {
                _log.Trace("保存结果开始");
                HSSFWorkbook wk = null;
                var date = results.StartTime.Substring(0, 8);
                //保存project信息
                string dirPath = Path.Combine(Device.LogPath, results.BatchID);
                if (!Directory.Exists(dirPath))
                {
                    Directory.CreateDirectory(dirPath);
                }
                //保存project信息
                var fullPath = Path.Combine(dirPath, string.Format("{0}_API_{1}_{2}.xls", results.BatchID, date, cID));//日

                if (!File.Exists(fullPath))
                {
                    wk = new HSSFWorkbook();
                    ISheet sheet = wk.CreateSheet("API");
                    IRow irow = sheet.CreateRow(0);
                    IRow irow2 = sheet.CreateRow(1);
                    irow.CreateCell(0).SetCellValue("BatchID");
                    irow2.CreateCell(0).SetCellValue(results.BatchID);
                    irow.CreateCell(1).SetCellValue("PanelID");
                    irow2.CreateCell(1).SetCellValue(results.PannelID);
                    irow.CreateCell(2).SetCellValue("OperatorID");
                    irow2.CreateCell(2).SetCellValue(results.OperatorID);
                    irow.CreateCell(3).SetCellValue("LineNO");
                    irow2.CreateCell(3).SetCellValue(results.LineNO);
                    irow.CreateCell(4).SetCellValue("StartTime");
                    irow2.CreateCell(4).SetCellValue(results.StartTime);
                    irow.CreateCell(5).SetCellValue("Duration");
                    irow2.CreateCell(5).SetCellValue(results.Duaration);
                    irow.CreateCell(6).SetCellValue("Result");
                    irow2.CreateCell(6).SetCellValue(results.InspResult == 1 ? "OK" : "NG");
                    irow.CreateCell(7).SetCellValue("ErrCode");
                    irow2.CreateCell(7).SetCellValue(results.ErrCode);
                    var sortResult = results.ListResults.OrderBy(item => item.ItemName).ToList();
                    for (int i = 0; i < sortResult.Count; i++)
                    {
                        irow.CreateCell(8 + i).SetCellValue(sortResult[i].ItemName);
                        irow2.CreateCell(8 + i).SetCellValue(sortResult[i].Msg);
                    }
                    using (FileStream fsCreate = File.Open(fullPath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
                    {
                        wk.Write(fsCreate);
                    }
                }
                else
                {
                    using (FileStream fsRead = File.OpenRead(fullPath))
                    {
                        wk = new HSSFWorkbook(fsRead);
                    }
                    ISheet sheet = wk.GetSheet("API");

                    IRow ir = sheet.CreateRow(sheet.LastRowNum + 1);

                    #region 若第一行无表头，则创建表头
                    IRow irfirst = sheet.GetRow(0);//获取第一行
                    string[] str = new string[results.ListResults.Count + 8];
                    var firlist = irfirst.Cells.AsEnumerable().ToList().Select(x => x.ToString()).ToList();//现有的表头
                    var headlist = new List<string>(firlist.ToArray());
                    for (int i = 0; i < str.Count(); i++)
                    {
                        if (i >= irfirst.Cells.Count)
                        {
                            irfirst.CreateCell(i).SetCellValue("");
                        }
                        if (irfirst.GetCell(i) == null)
                            str[i] = "";
                        else
                            str[i] = irfirst.GetCell(i).ToString();
                    }
                    for (int i = 0; i < conststr.Count; i++)
                        if (irfirst.GetCell(i).ToString() == "") irfirst.GetCell(i).SetCellValue(conststr[i]);
                    if (results.ListResults.Count > 0)
                    {
                        var sortResultt = results.ListResults.OrderBy(item => item.ItemName).Distinct().ToList();
                        var sortstr = sortResultt.Select(x => x.ItemName).ToList();
                        var sortlist = conststr.Union(sortstr).ToList();//本次需要更新的所有表头
                        var except = sortlist.Except(firlist).ToList();
                        headlist = firlist.Union(except).ToList();//
                        if (except != null)
                        {
                            int tcount = firlist.Count(x => x != "");
                            for (int i = 0; i < except.Count(); i++)
                            {
                                var cellstr = irfirst.GetCell(tcount + i).ToString();
                                if (cellstr == "")
                                    irfirst.GetCell(tcount + i).SetCellValue(except[i]);
                                else
                                {

                                }
                            }
                        }
                    }
                    #endregion 若第一行无表头，则创建表头

                    ir.CreateCell(0).SetCellValue(results.BatchID);

                    ir.CreateCell(1).SetCellValue(results.PannelID);

                    ir.CreateCell(2).SetCellValue(results.OperatorID);
                    ir.CreateCell(3).SetCellValue(results.LineNO);
                    ir.CreateCell(4).SetCellValue(results.StartTime);

                    ir.CreateCell(5).SetCellValue(results.Duaration);

                    ir.CreateCell(6).SetCellValue(results.InspResult == 1 ? "OK" : "NG");
                    ir.CreateCell(7).SetCellValue(results.ErrCode);
                    var sortResult = results.ListResults.OrderBy(item => item.ItemName).ToList();
                    for (int i = 0; i < sortResult.Count; i++)
                    {
                        var index = headlist.FindIndex(x => x.Equals(sortResult[i].ItemName));
                        ir.CreateCell(index).SetCellValue(sortResult[i].Msg + GetCoordinatesStr(sortResult[i] as ResultDisplayRowResult));
                    }
                    using (FileStream fsWrite = File.OpenWrite(fullPath))
                    {
                        wk.Write(fsWrite);
                    }
                }
                wk = null;
                GC.Collect();
                _log.Trace("保存结果完成");
                return true;
            }
            catch (Exception ex)
            {
                _log.Error("结果保存失败" + ex.Message + DateTime.Now.ToString("HH:mm:ss"), Device.Parameter.CameraList[0].CameraName);
                return false;
            }
        }

        public string GetCoordinatesStr(ResultDisplayRowResult arr)
        {
            if (arr == null || arr.ErrCol == null)
            {
                return "";
            }
            StringBuilder sb = new StringBuilder();
            if (arr.ErrCol.Count > 0 && arr.ErrCol.Count <= 50)
            {
                for (int i = 0; i < arr.ErrCol.Count; i++)
                {
                    sb.AppendFormat("({0},{1},{2},{3})", arr.ErrCol[i], arr.ErrRow[i], arr.Index, arr.ErrType[i]);
                }
            }
            else if (arr.ErrCol.Count > 50)
            {
                for (int i = 0; i < 50; i++)
                {
                    sb.AppendFormat("({0},{1},{2},{3})", arr.ErrCol[i], arr.ErrRow[i], arr.Index, arr.ErrType[i]);
                }
            }
            return sb.ToString();
        }
        #endregion 保存检测记录
    }
    public class ExcelHelper : IDisposable
    {
        private string fileName = null; //文件名
        private IWorkbook workbook = null;
        private FileStream fs = null;
        private bool disposed;

        public ExcelHelper(string fileName)
        {
            this.fileName = fileName;
            disposed = false;
        }

        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            ISheet sheet = null;

            fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                workbook = new XSSFWorkbook();
            else if (fileName.IndexOf(".xls") > 0) // 2003版本
                workbook = new HSSFWorkbook();

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return -1;
                }

                if (isColumnWritten == true) //写入DataTable的列名
                {
                    IRow row = sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }

                for (i = 0; i < data.Rows.Count; ++i)
                {
                    IRow row = sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; ++j)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    ++count;
                }
                workbook.Write(fs); //写入到excel
                return count;
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
                return -1;
            }
        }

        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name="sheetName">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            try
            {
                fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                    workbook = new XSSFWorkbook(fs);
                else if (fileName.IndexOf(".xls") > 0) // 2003版本
                    workbook = new HSSFWorkbook(fs);

                if (sheetName != null)
                {
                    sheet = workbook.GetSheet(sheetName);
                    if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                    {
                        sheet = workbook.GetSheetAt(0);
                    }
                }
                else
                {
                    sheet = workbook.GetSheetAt(0);
                }
                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数

                    if (isFirstRowColumn)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.StringCellValue;
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        startRow = sheet.FirstRowNum + 1;
                    }
                    else
                    {
                        startRow = sheet.FirstRowNum;
                    }

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;
                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                dataRow[j] = row.GetCell(j).ToString();
                        }
                        data.Rows.Add(dataRow);
                    }
                }

                return data;
            }
            catch (Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    if (fs != null)
                        fs.Close();
                }

                fs = null;
                disposed = true;
            }
        }
    }
}
